package com.vimi8.ebb.auth.service;

import com.vimi8.ebb.auth.dto.AuthUserDto;
import com.vimi8.ebb.auth.dto.OrgDto;
import com.vimi8.ebb.auth.model.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by shiyong on 2016/10/11.
 */
@Mapper
public interface OrganizationService {

    /**
     * 添加组织信息
     * */
    @Insert(" INSERT  INTO `organization`(`client_id`,`org_class`,`org_corp`,`address_code`,`address_detail`,`org_name`,`remark`,`org_images`,`org_description`,`create_time`)  " +
            " VALUES (#{clientId},#{orgClass},#{orgCorp},#{addressCode},#{addressDetail},#{orgName},#{remark},#{orgImages},#{orgClassDescription},CURRENT_TIMESTAMP())")
    public int insertOrganization(Organization organization);
    /**
     * 更新织信息
     * */
    @Update(" update organization set address_code=#{addressCode},address_detail=#{addressDetail},org_name=#{orgName}," +
            "org_description=#{orgDescription} " +
            " where client_id=#{clientId}")
    public int updateOrganization(Organization organization);
    /**
     * 更新织信息
     * */
    @Update(" update organization set is_approval=#{isApproval}"+
            " where client_id=#{clientId}")
    public int updateOrganizationApproval(@Param("clientId") String clientId,@Param("isApproval") int isApproval);
    /**
     * 更新织名称
     * */
    @Update(" update organization set org_name=#{orgName} " +
            " where  client_id=#{clientId}")
    public int updateOrganizationName(Organization organization);

    /**
     * 查询组织信息（包括b端、服务商特色角色）
     * */
    @Select("select client_id as clientId,org_corp as orgCorp,org_class as orgClass,address_code as addressCode,address_detail as addressDetail,org_name as orgClassName,org_name as orgName," +
            "org_description as orgClassDescription from organization where client_id=#{clientId}")
    public Organization getOrganization(String clientId);

    /**
     * 查询组织信息
     * */
    @Select("select client_id as clientId,org_class as orgClass,org_corp as orgCorp,address_code as addressCode,address_detail as addressDetail,org_name as orgClassName," +
            "org_description as orgClassDescription from organization where client_id=#{clientId} and org_class!='org_role'")
    public Organization getOrganizationByClient(String clientId);
    /**
     * 查询组织信息
     * */
    @Select("<script> select count(client_id) from organization where org_class=#{orgClass}" +
            "  <if test=\"geoCode!=null and geoCode!=''\"> and (address_code like CONCAT('%',#{geoCode},'%') or address_code =#{addressCode})</if>" +
            " </script>")
    public int getOrganizationTotalByClientClass(@Param("orgClass") String orgClass, @Param("geoCode") String geoCode, @Param("addressCode") String addressCode);

    /**
     * 根据组织类型查询相关模板信息
     * */
    @Select("select org_class as orgClass,grant_type as grantTypes,allow_users_to_join as allowUsersToJoin,auto_resources as autoApproveResources," +
            "auto_scopes as autoApproveScopes,manu_resources as manuApproveResources,manu_scopes as manuApproveScopes,org_class_name as orgClassName, " +
            "org_class_description as orgClassDescription from organization_class where org_class=#{orgClass}")
    public Organization getOrganizationTempleteByOrgClass(String orgClass);

    /**
     * 添加组织部门信息
    * */
    @Insert(" INSERT  INTO `organization_dept`(`client_id`,`dept_id`,`dept_parent_id`,`dept_name`,`dept_resources`,`dept_scopes`,`dept_place_detail`,`dept_place`,`create_time`)  " +
            " VALUES (#{clientId},#{deptId},#{deptParentId},#{deptName},#{deptResources},#{deptScopes},#{deptPlaceDetail},#{deptPlace},CURRENT_TIMESTAMP())")
    public int insertOrganizationDept(OrganizationDept organizationDept);


    /**
     * 删除组织
     * @param clientId
     * @return
     */
    @Delete("delete from organization where client_id=#{clientId}")
    public int deleteOrganization(String clientId);

    /**
     * 更新织部门信息
     * */
    @Update(" update organization_dept set dept_name=#{deptName},dept_resources=#{deptResources},dept_scopes=#{deptScopes}," +
            "dept_place_detail=#{deptPlaceDetail},dept_place=#{deptPlace}  " +
            " where  client_id=#{clientId} and dept_id=#{deptId}")
    public int updateOrganizationDept(OrganizationDept organizationDept);
    /**
     * 查询组织部门信息
     * */
    @Select("<script>select client_id as clientId,dept_id as deptId,dept_parent_id as deptParentId ,dept_name as deptName,dept_resources as deptResources,dept_scopes as deptScopes,dept_place_detail as deptPlaceDetail,dept_place as deptPlace" +
            "from organization_dept where client_id=#{clientId} and 1=1 " +
            " <if test=\"deptLevel!=null and deptLevel!=''\"> and dept_id=#{deptLevel}</if> " +
            "</script>")
    public OrganizationDept getOrganizationDept(@Param("clientId") String clientId, @Param("deptLevel") String deptLevel);
    /**
     * 查询组织顶级部门信息
     * */
    @Select("select client_id as clientId,dept_id as deptId,dept_parent_id as deptParentId ,dept_name as deptName,dept_resources as deptResources,dept_scopes as deptScopes,dept_place_detail as deptPlaceDetail,dept_place as deptPlace" +
            " from organization_dept where client_id=#{clientId} and dept_parent_id ='-1' and dept_id='0'")
    public OrganizationDept getParentOrganizationDept(@Param("clientId") String clientId);

    /**
     * 查询组织顶级部门信息
     * */
    @Select("select client_id as clientId,dept_id as deptId,dept_parent_id as deptParentId ,dept_name as deptName,dept_resources as deptResources,dept_scopes as deptScopes,dept_place_detail as deptPlaceDetail,dept_place as deptPlace" +
            " from organization_dept where client_id=#{clientId} and dept_id=#{deptId}")
    public OrganizationDept getParentOrganizationDeptByDeptId(@Param("clientId") String clientId, @Param("deptId") String deptId);

    /**
     * 根据clientId查询员工信息(除了农场主)
     * */
    @Select("<script> SELECT IFNULL(count(DISTINCT(e.user_id)),0) FROM organization o,organization_emp e,oauth_user u,oauth_client_user ou WHERE o.client_id=e.client_id AND u.user_id=ou.user_id AND 1=1" +
            " <if test=\"job!=null and job!=''\"> and e.job like CONCAT('%',#{job},'%')</if> " +
            " <if test=\"isPoor!=null and isPoor!=''\"> and u.is_shake_poverty = #{isPoor}</if> " +
            " <if test=\"status!=null and status!=''\"> and e.is_freeze = #{status}</if> " +
            " and (e.`job` like concat('%','org_shareholder','%')) " +
            "  <if test=\"key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " and o.org_corp!=ou.user_id and u.user_id=e.user_id and o.client_id=#{clientId} </script>")
    public int getAuthUserCountForHolder(@Param("clientId") String clientId,
                                         @Param("job") String job,
                                         @Param("key") String key,
                                         @Param("status") String status,
                                         @Param("isPoor") String isPoor);


    /**
     * 查询股东列表
     * */
    @Select("<script> SELECT IFNULL(count(DISTINCT(e.user_id)),0) FROM organization o,organization_emp e,oauth_user u,oauth_client_user ou WHERE o.client_id=e.client_id AND u.user_id=ou.user_id AND 1=1" +
            " <if test=\"job!=null and job!=''\"> and e.job like CONCAT('%',#{job},'%')</if> " +
            " <if test=\"isPoor!=null and isPoor!=''\"> and u.is_shake_poverty = #{isPoor}</if> " +
            " <if test=\"status!=null and status!=''\"> and e.is_freeze = #{status}</if> " +
            " and (e.`job` like concat('%','org_shareholder','%')) " +
            "  <if test=\"key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " and o.org_corp!=ou.user_id and u.user_id=e.user_id and o.client_id=#{clientId} </script>")
    public int getShareholderCountForHolder(@Param("clientId") String clientId,
                                         @Param("job") String job,
                                         @Param("key") String key,
                                         @Param("status") String status,
                                         @Param("isPoor") String isPoor);

    /**
     * 根据clientId查询员工信息(除了农场主)
     * */
    @Select("<script> SELECT IFNULL(count(DISTINCT(e.user_id)),0) FROM organization o,organization_emp e,oauth_user u,oauth_client_user ou WHERE o.client_id=e.client_id AND u.user_id=ou.user_id AND 1=1" +
            " <if test=\"job!=null and job!=''\"> and e.job like CONCAT('%',#{job},'%')</if> " +
            " <if test=\"isPoor!=null and isPoor!=''\"> and u.is_shake_poverty = #{isPoor}</if> " +
            " <if test=\"status!=null and status!=''\"> and e.is_freeze = #{status}</if> " +
            " and (e.`job`='org_manager' OR e.`job` = 'org_emp' OR e.`job` = 'org_senior_manager' OR e.`job` = 'org_shareholder_emp' OR e.`job` = 'org_shareholder_manager')  " +
            "  <if test=\"key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " and o.org_corp!=ou.user_id and u.user_id=e.user_id and o.client_id=#{clientId} </script>")
    public int getAuthUserCountForEmp(@Param("clientId") String clientId,
                                @Param("job") String job,
                                @Param("key") String key,
                                @Param("status") String status,
                                @Param("isPoor") String isPoor);

    /**
     * 根据clientId查询员工信息（除了农场主）
     * */
    @Select("<script> SELECT DISTINCT(e.user_id) AS userId,e.job as job,e.is_freeze as isFreeze,u.is_shake_poverty as isShakePoverty,ou.client_id AS clientId,ou.open_id AS openId,u.head_img AS headImg," +
            "u.id_card AS idCard,u.user_name AS userName,u.phone_number AS phoneNumber,u.real_name AS realName,u.nick_name AS nickName,sex," +
            "u.address_code AS addressCode,u.address_detail AS addressDetail,u.default_client_id AS defaultClientId FROM organization o," +
            " organization_emp e,oauth_user u,oauth_client_user ou WHERE o.client_id=e.client_id AND u.user_id=ou.user_id AND 1=1" +
            " <if test=\"job!=null and job!=''\"> and e.job like CONCAT('%',#{job},'%')</if> " +
            " <if test=\"isPoor!=null and isPoor!=''\"> and u.is_shake_poverty = #{isPoor}</if> " +
            " <if test=\"status!=null and status!=''\"> and e.is_freeze = #{status}</if> " +
            "  and (e.`job` like concat('%','org_shareholder','%')) " +
            "  <if test=\"key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " and o.org_corp!=ou.user_id and u.user_id=e.user_id and o.client_id=#{clientId} GROUP BY userId limit #{start},#{size} </script>")
    public List<AuthUserDto> getAuthUserForHolder(@Param("clientId") String clientId,
                                         @Param("job") String job,
                                         @Param("key") String key,
                                         @Param("status") String status,
                                         @Param("isPoor") String isPoor,
                                         @Param("start") int start,
                                         @Param("size") int size);

    /**
     * 根据clientId查询员工信息（除了农场主）
     * */
    @Select("<script> SELECT DISTINCT(e.user_id) AS userId,e.job as job,e.is_freeze as isFreeze,u.is_shake_poverty as isShakePoverty,ou.client_id AS clientId,ou.open_id AS openId,u.head_img AS headImg," +
            "u.id_card AS idCard,u.user_name AS userName,u.phone_number AS phoneNumber,u.real_name AS realName,u.nick_name AS nickName,sex," +
            "u.address_code AS addressCode,u.address_detail AS addressDetail,u.default_client_id AS defaultClientId FROM organization o," +
            " organization_emp e,oauth_user u,oauth_client_user ou WHERE o.client_id=e.client_id AND u.user_id=ou.user_id AND 1=1" +
            " <if test=\"job!=null and job!=''\"> and e.job like CONCAT('%',#{job},'%')</if> " +
            " <if test=\"isPoor!=null and isPoor!=''\"> and u.is_shake_poverty = #{isPoor}</if> " +
            " <if test=\"status!=null and status!=''\"> and e.is_freeze = #{status}</if> " +
            "  and (e.`job`='org_manager' OR e.`job` = 'org_emp' OR e.`job` = 'org_senior_manager' OR e.`job` = 'org_shareholder_emp' OR e.`job` = 'org_shareholder_manager')  " +
            "  <if test=\"key!=null and key!=''\"> and (u.user_name like CONCAT('%',#{key},'%') or u.phone_number like CONCAT('%',#{key},'%') or u.real_name like CONCAT('%',#{key},'%')) </if> " +
            " and o.org_corp!=ou.user_id and u.user_id=e.user_id and o.client_id=#{clientId} GROUP BY userId limit #{start},#{size} </script>")
    public List<AuthUserDto> getAuthUserForEmp(@Param("clientId") String clientId,
                                         @Param("job") String job,
                                         @Param("key") String key,
                                         @Param("status") String status,
                                         @Param("isPoor") String isPoor,
                                         @Param("start") int start,
                                         @Param("size") int size);

    /**
     * 根据clientId和scopes查询我的雇主（农场主）信息
     * */
    @Select("select u.user_id as userId,ou.scopes as scopes,ou.client_id as clientId,ou.open_id as openId,o.org_class as clientOrgClass,o.org_name as orgClassName," +
            "u.head_img as headImg,u.id_card as idCard,u.user_name as userName,u.phone_number as phoneNumber,u.real_name as realName,u.nick_name as nickName," +
            "sex,u.address_code as addressCode,u.address_detail as addressDetail,u.default_client_id as defaultClientId" +
            " from organization o, organization_emp e,oauth_user u,oauth_client_user ou" +
            " where o.client_id=e.client_id and ou.client_id=o.client_id and u.user_id=ou.user_id and 1=1 " +
          " and o.org_corp=u.user_id "+
            "  and u.user_id=e.user_id and o.client_id=#{clientId} ")
    public AuthUserDto getMyFarmerUser(@Param("clientId") String clientId);


    /*
    * 添加组织部门员工信息
    * */
    @Insert(" INSERT  INTO `organization_emp`(`client_id`,`dept_id`,`user_id`,`job`,`hire_date`,`create_time`)  " +
            " VALUES (#{clientId},#{deptId},#{userId},#{job},#{hireDate},CURRENT_TIMESTAMP())")
    public int insertOrganizationEmp(OrganizationEmp organizationEmp);

    /**
     * 查询组织部门员工信息
     * */
    @Select(" select client_id as clientId,dept_id as deptId,user_id as userId,hireDate as hire_date from organization_emp where dept_id=#{deptId}")
    public OrganizationEmp getOrganizationEmp(String deptId);

    /**
     * 根据组织类型询组织部门员工总数
     * */
    @Select("<script> select count(distinct(oe.user_id)) from organization o,organization_emp oe,oauth_user ou where o.client_id=oe.client_id and oe.user_id=ou.user_id and o.org_class=#{orgClass} " +
            " <if test=\"geoCode!=null and geoCode!=''\"> and ou.address_code LIKE CONCAT('%',#{geoCode},'%')</if> " +
            " <if test=\"placeGroup!=null and placeGroup!=''\"> and ou.address_detail LIKE CONCAT('%',#{placeGroup},'%')</if> " +
            "</script>")
    public int getOrganizationEmpTotalByOrgClass(@Param("orgClass") String orgClass, @Param("geoCode") String geoCode, @Param("placeGroup") String placeGroup);

    /**
     * 查询组织部门员工信息
     * */
    @Delete("delete from organization_emp  where client_id=#{clientId} and user_id in(select user_id from oauth_client_user where open_id=#{openId})")
    public int deleteOrganizationEmpByClientIdAnd(@Param("clientId") String clientId, @Param("openId") String openId);


    /**
     * 员工申请访问资源记录
     * */
    @Insert(" INSERT  INTO `user_apply_client_resource`(`client_id`,`user_id`,`resource_id`,`scope`,`job`,`create_time`)  " +
            " VALUES (#{clientId},#{userId},#{resourceId},#{scope},#{job},CURRENT_TIMESTAMP())")
    public int insertUserApplyClientResource(UserApplyClientResource userApplyClientResource);

    /**
     * 删掉申请授权记录
     * */
    @Select("select user_id as userId,client_id as userId,resource_id as resourceId from user_apply_client_resource where user_id=#{userId} and client_id=#{clientId}")
    public UserApplyClientResource getUserApplyClientResource(@Param("userId") String userId, @Param("clientId") String clientId);

    /**
     * 删掉申请授权记录
     * */
    @Delete("delete from user_apply_client_resource where user_id=#{userId} and client_id=#{clientId}")
    public int deleteUserApplyClientResource(@Param("userId") String userId, @Param("clientId") String clientId);
    /**
     * 删掉申请授权记录
     * */
    @Delete("update user_apply_client_resource set is_approval=#{isApproval} where user_id=#{userId} and client_id=#{clientId} and job=#{job}")
    public int updateApplyClientResource(@Param("userId") String userId, @Param("clientId") String clientId,@Param("job") String job,@Param("isApproval") int isApproval);


    /**
    * 查询组织类型对应的授权信息
    * */
    @Select("SELECT org_class as orgClass,org_class_name as orgClassName,org_class_description as orgClassDescription,grant_type as grantTypes,allow_users_to_join as allowUsersToJoin,auto_resources as autoApproveResources,auto_scopes as autoApproveScopes," +
            "manu_resources as manuApproveResources,manu_scopes as manuApproveScopes from organization_class where org_class = #{orgClass}")
    public OrganizationClass getOrganizationClassByOrgClass(@Param("orgClass") String orgClass);

    @Update("update organization_emp oe,oauth_client_user ou set oe.job=#{job} WHERE oe.client_id=ou.client_id and ou.open_id=#{openId} and  oe.client_id=#{clientId} and oe.user_id=#{userId}")
    public int updateJob(@Param("clientId") String clientId, @Param("openId") String openId, @Param("job") String job, @Param("userId") String userId);

    /**
     * 根据组织形式类型（1：农场组织，2：政府组织，3：vimi运营组织）查询组织形式列表
     * */
    @Select("SELECT org_class as orgClass,org_class_name as orgClassName,org_class_description as orgClassDescription,grant_type as grantTypes,allow_users_to_join as allowUsersToJoin,auto_resources as autoApproveResources,auto_scopes as autoApproveScopes," +
            "manu_resources as manuApproveResources,manu_scopes as manuApproveScopes,org_class_type as orgClassType from organization_class where org_class_type = #{orgClassType}")
    public List<OrganizationClass> getOrganizationClassByOrgClassType(@Param("orgClassType") Integer orgClassType);

    /**
     * 添加组织形式
     * */
    @Insert("insert into organization_class(org_class,org_class_name,org_class_description ,grant_type,allow_users_to_join,auto_resources,auto_scopes," +
            "manu_resources,manu_scopes,org_class_type)values(#{orgClass},#{orgClassName},#{orgClassDescription},#{grantType},#{allowUsersToJoin},#{autoResources},#{autoScopes}," +
            "#{manuResources},#{manuScopes},#{orgClassType})")
    public int insrertOrganizationClass(OrganizationClass organizationClass);

    /**
     * 根据openId 查询已加入，自己的农场
     * @param openId
     * @return
     */
    @Select("select op.job as job,o.client_id as orgId,o.org_name as orgName,o.org_class as orgClass,o.org_images as orgImages,o.address_code as addressCode," +
            "o.address_detail as addressDetail from organization_emp as op,organization as o,oauth_client_user as ocu where op.client_id=o.client_id and" +
            " op.job!='poor_people' and ocu.open_id=#{openId} and op.user_id=ocu.user_id limit #{start},#{size}")
    public List<OrgDto> getOrganizationInfoByUser(@Param("openId") String openId, @Param("start")int start, @Param("size")int size);

    @Select("select count(1) from organization_emp as op,organization as o,oauth_client_user as ocu where op.client_id=o.client_id and "+
            " op.job!='poor_people' and ocu.open_id=#{openId} and op.user_id=ocu.user_id ")
    public int getOrganizationCountByUser(String openId);


    /**
     * 查询组织组织下股东数
     * */
    @Select("select count(user_id) as memberTotal from organization_emp where client_id=#{clientId} and job like concat('%','org_shareholder','%')")
    public Integer getOrgShareHolderTotal(@Param("clientId") String clientId);

    /**
     * 查询组织组织下员工数
     * */
    @Select("select count(user_id) as memberTotal from organization_emp where client_id=#{clientId} and (job!='org_corp' and job !='org_shareholder') ")
    public Integer getOrgEmpTotal(@Param("clientId") String clientId);


    /**
     * 删除组织
     * @param clientId
     * @return
     */
    @Delete("delete from organization_emp where client_id=#{clientId} and user_id=#{userId} and job=#{job}" )
    public int deleteOrganizationEmp(@Param("clientId") String clientId,@Param("userId")String userId,@Param("job")String job);
}
